﻿use BankTest
go

--1）编写视图实现查询出所有银行卡账户信息，显示卡号，身份证，姓名，余额。
create view v_account 
as
select CardNo,AccountCode,RealName,CardMoney from AccountInfo a
join BankCard b on b.AccountId = a.AccountId
go
select * from v_account
go


--2）行转列常用做法: group by + sum(case when) /+count(case when)  数据分析+ over (paritition by, order by)
--​     提示：  pivot

create database sec
go
use sec
go
create table secc
(
[year] int,
[month] int,
amount float
)
go
insert into secc([year],[month],amount) values
(1991,1,1.1),
(1991,2,1.2),
(1991,3,1.3),
(1991,4,1.4),
(1992,1,2.1),
(1992,2,2.2),
(1992,3,2.3),
(1992,4,2.4)
go
select * from secc
go

------
select * from secc
pivot(
MIN(amount) for [month] in ([1],[2],[3],[4])
) as aa

------
select [year],
MIN((case when [month] = 1 then amount else 404 end))m1,
MIN((case when [month] = 2 then amount else 404 end))m2,
MIN((case when [month] = 3 then amount else 404 end))m3,
MIN((case when [month] = 4 then amount else 404 end))m4
from secc
group by [year]


--列转行 union
--drop table stu
create table stu(
name varchar(20),
english int,
maths int,
music int
)
go

insert into stu(name,english,maths,music) values
('Jim',90,88,99)
go

-----
select name,'english' [subject],90 score from stu
union
select name,'maths' [subject],88 score from stu
union
select name,'music' [subject],99 score from stu
go

-----
select name,subject,score from stu
unpivot(
score for subject in(english,maths,music)
)as v
go